﻿using System;
using System.Collections.Generic;
using System.Data;
using CoreLab.Oracle;
using LMD.Lib.Oracle;
using LMD.Lib.DataClass;

namespace Controller
{
    /// <summary>
    /// Summary description for Survey Reason Controller
    /// </summary>
    public class NPSReasonController : ControllerBase
    {
        public int InsertNPSReasonData(Entities.NPSReason ciReason)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "out_new_id";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Output;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_REASON_CATEGORY_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciReason.iNPSReasonCategoryID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TITLE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciReason.sxTitle;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DETAIL";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciReason.sxDetail;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_I_P_NPS_REASON";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
            return Convert.ToInt32(lsParams[0].Value);
        }

        public void UpdateNPSReasonData(Entities.NPSReason ciReason)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_REASON_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciReason.iID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TITLE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciReason.sxTitle;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DETAIL";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciReason.sxDetail;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_REASON_CATEGORY_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciReason.iNPSReasonCategoryID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciReason.sxUpdater;

            String sxQuery = "PR_U_P_NPS_REASON";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public void DeleteNPSReasonData(Entities.NPSReason ciReason)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_REASON_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciReason.iID;

            String sxQuery = "PR_D_P_NPS_REASON";
            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public List<Entities.NPSReason> NPSReasonList
        {
            get
            {
                return getNPSReason(string.Empty,false);
            }
        }

        public List<Entities.NPSReason> DetailedNPSReasonList
        {
            get
            {
                return getNPSReason(string.Empty,true);
            }
        }

        public List<Entities.NPSReason> find(string Search)
        {
            return getNPSReason(Search, true);
        }

        public List<Entities.NPSReason> getNPSReason(string search,bool isDetailed)
        {
            DBLib dbl = new DBLib();

            String sxQuery =
                @"select 
                    P_NPS_REASON_ID,
                    P_NPS_REASON_CATEGORY_ID,
	                TITLE,
	                DETAIL,
	                CREATE_BY,
                    CREATE_DATE,
                    UPDATE_BY,
                    UPDATE_DATE,
                    VALID_FROM
                from 
                    P_NPS_REASON
                where 
                   NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    upper(Title) like upper('%" + search + @"%')
                ";
            DataTable dt = dbl.GetDataTable(sxQuery);
            NPSReasonCategoryController SRController = new NPSReasonCategoryController();
            List<Entities.NPSReason> ReasonList = new List<Entities.NPSReason>();
            foreach (DataRow row in dt.Rows)
            {
                Entities.NPSReason Reason = new Entities.NPSReason();
                Reason.iID = Convert.ToInt16(row["P_NPS_REASON_ID"].ToString());
                Reason.iNPSReasonCategoryID = Convert.ToInt16(row["P_NPS_REASON_CATEGORY_ID"].ToString());
                Reason.sxTitle = row["TITLE"].ToString();
                Reason.sxDetail = row["DETAIL"].ToString();
                Reason.sxCreator = row["CREATE_BY"].ToString();
                Reason.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                Reason.sxUpdater = row["UPDATE_BY"].ToString();
                Reason.dtmUpdated = DateTime.Parse(row["UPDATE_DATE"].ToString());
                Reason.dtmValidFrom = DateTime.Parse(row["VALID_FROM"].ToString());
                if (isDetailed)
                {
                    Reason.oNPSReasonCategory = SRController.getNPSReasonCategory(Reason.iNPSReasonCategoryID);
                }
                ReasonList.Add(Reason);
            }
            return ReasonList;
        }

        public List<Entities.NPSReason> getNPSReasonByCategory(string id)
        {
            DBLib dbl = new DBLib();

            String sxQuery =
                @"select 
                    P_NPS_REASON_ID,
                    P_NPS_REASON_CATEGORY_ID,
	                TITLE,
	                DETAIL,
	               CREATE_BY,
                    CREATE_DATE,
                    UPDATE_BY,
                    UPDATE_DATE,VALID_FROM
                from 
                    P_NPS_REASON
                where 
                 NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    P_NPS_REASON_CATEGORY_ID = " + id ;
            DataTable dt = dbl.GetDataTable(sxQuery);
            List<Entities.NPSReason> ReasonList = new List<Entities.NPSReason>();
            foreach (DataRow row in dt.Rows)
            {
                Entities.NPSReason Reason = new Entities.NPSReason();
                Reason.iID = Convert.ToInt16(row["P_NPS_REASON_ID"].ToString());
                Reason.iNPSReasonCategoryID = Convert.ToInt16(row["P_NPS_REASON_CATEGORY_ID"].ToString());
                Reason.sxTitle = row["TITLE"].ToString();
                Reason.sxDetail = row["DETAIL"].ToString();
                Reason.sxCreator = row["CREATE_BY"].ToString();
                Reason.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                Reason.sxUpdater = row["UPDATE_BY"].ToString();
                Reason.dtmUpdated = DateTime.Parse(row["UPDATE_DATE"].ToString());
                Reason.dtmValidFrom = DateTime.Parse(row["VALID_FROM"].ToString());
            }
            return ReasonList;
        }

        public Entities.NPSReason getNPSReason(int ReasonID, bool isDetailed)
        {
            DBLib dbl = new DBLib();
            String sxQuery =
                @"select 
                    P_NPS_REASON_ID,
                    P_NPS_REASON_CATEGORY_ID,
	                TITLE,
	                DETAIL,
	               CREATE_BY,
                    CREATE_DATE,
                    UPDATE_BY,
                    UPDATE_DATE,VALID_FROM
                from 
                    P_NPS_REASON
                where 
                   NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    P_NPS_REASON_ID = :prmID
                ";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ReasonID;
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            Entities.NPSReason Reason = new Entities.NPSReason();
            try
            {
                Reason.iID = Convert.ToInt16(dt.Rows[0]["P_NPS_REASON_ID"].ToString());
                Reason.iNPSReasonCategoryID = Convert.ToInt16(dt.Rows[0]["P_NPS_REASON_CATEGORY_ID"].ToString());
                Reason.sxTitle = dt.Rows[0]["TITLE"].ToString();
                Reason.sxDetail = dt.Rows[0]["DETAIL"].ToString();
                Reason.sxCreator = dt.Rows[0]["CREATE_BY"].ToString();
                Reason.dtmCreated = DateTime.Parse(dt.Rows[0]["CREATE_DATE"].ToString());
                Reason.sxUpdater = dt.Rows[0]["UPDATE_BY"].ToString();
                Reason.dtmUpdated = DateTime.Parse(dt.Rows[0]["UPDATE_DATE"].ToString());
                Reason.dtmValidFrom = DateTime.Parse(dt.Rows[0]["VALID_FROM"].ToString());
                if (isDetailed)
                {
                    NPSReasonCategoryController SRController = new NPSReasonCategoryController();
                    Reason.oNPSReasonCategory = SRController.getNPSReasonCategory(Reason.iNPSReasonCategoryID);
                }
            }
            catch (Exception p) { errLog = p.ToString(); }
            return Reason;
        }


        
    }
}